var express = require('express');
var router = express.Router();
var mysql = require('mysql');

//配置好数据库连接
function createConn() {
    var conn = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '123456',
        database: 'stumanager'
    });
    conn.on('error', function () {
        conn.connect(err => {
            if (err) console.log('数据库连接失败！\n' + err.stack);
            else console.log('数据库连接成功！');
        });
    });
    return conn;
}

/* GET home page. */
router.get('/', function (req, res, next) {

    var conn = createConn();
    if (conn) {
        var strSQL = 'select * from student'
        conn.query(strSQL, (err, result) => {
            conn.end();
            if (err) {
                console.log('查询出错！');
                res.send('查询出错！');
            } else {
                res.render('studentlist', {
                    title: '学生信息表',
                    data: result
                })
            }
        });
    }
});

router.post('/add', function (req, res) {
    var conn = createConn();
    if (conn) {

        var stu = {
            sNo: req.body.sNo,
            sName: req.body.sName,
            sSex: req.body.sSex,
            sBirthday: req.body.sBirthday,
            class: req.body.class
        };

        var strSQL = "insert into student (sNo, sName, sSex, sBirthday, class) values ('" + stu.sNo + "','" + stu.sName + "','" + stu.sSex + "','" + stu.sBirthday + "','" + stu.class + "')";
        conn.query(strSQL, (err, result) => {
            conn.end();
            if (err) {
                console.log('添加数据出错！');
                res.send({
                    code: 1,
                    msg: '添加数据出错'
                });
            } else {
                res.send({
                    code: 0,
                    msg: 'OK'
                });
            }
        });
    }

});

router.post('/del', (req, res) => {
    var conn = createConn();
    if (conn) {

        var strSQL = "delete from student where sNo = '" + req.body.sNo + "'"
        conn.query(strSQL, (err, result) => {
            conn.end();
            if (err) {
                console.log('删除数据出错！\n' + err.stack);
                res.send({
                    code: 2,
                    msg: '删除数据出错'
                });
            } else {
                res.send({
                    code: 0,
                    msg: 'OK'
                });
            }
        });
    }

});


router.post('/modify', (req, res) => {
    var conn = createConn();
    if (conn) {

        var stu = {
            sNo: req.body.sNo,
            sName: req.body.sName,
            sSex: req.body.sSex,
            sBirthday: req.body.sBirthday,
            class: req.body.class
        };

        var strSQL = "update student set sNo = '" + stu.sNo + "', sName = '" + stu.sName + "' , sSex = '" + stu.sSex + "', sBirthday = '" + stu.sBirthday + "', class = '" + stu.class + "' where sNo = '" + stu.sNo + "'"
        conn.query(strSQL, (err, result) => {
            conn.end();
            if (err) {
                console.log('修改数据出错！\n' + err.stack);
                res.send({
                    code: 2,
                    msg: '修改数据出错'
                });
            } else {
                res.send({
                    code: 0,
                    msg: 'OK'
                });
            }
        });
    }

});

router.post('/filter', (req, res) => {
    var conn = createConn();
    if (conn) {

        var strSQL = "select * from student where class = '" + req.body.class + "'"
        conn.query(strSQL, (err, result) => {
            conn.end();
            if (err) {
                console.log('查询出错！');
                res.send('查询出错！');
            } else {
                res.render('studentlist', {
                    title: '学生信息表',
                    data: result
                })
            }
        });
    }

});


module.exports = router;
